package org.scct.chenhui.dao;

import org.scct.chenhui.model.BookBean;
import org.scct.chenhui.utils.DBUtils;

import java.lang.management.BufferPoolMXBean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDao {


    public List<BookBean> getAllBooks() {
        List<BookBean> bookBeans = new ArrayList<>();
        String sql = "SELECT bm_book.*,bm_book_publish.`name` as publishName,bm_book_type.type_name as typeName \n" +
                "from bm_book,bm_book_type,bm_book_publish \n" +
                "WHERE bm_book.publish_id=bm_book_publish.id and bm_book.type_id=bm_book_type.id";
        Connection connection = DBUtils.getConnection();
        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String bookName = rs.getString("book_name");
                int typeId = rs.getInt("type_id");
                String author = rs.getString("author");
                int publishId = rs.getInt("publish_id");
                float price = rs.getFloat("price");
                int number = rs.getInt("number");
                int status = rs.getInt("status");
                String remark = rs.getString("remark");
                String publishName = rs.getString("publishName");
                String typeName = rs.getString("typeName");
                BookBean book = new BookBean(id, bookName, typeId, typeName, author, publishId, publishName, price, number, status, remark);
                bookBeans.add(book);
            }
            pstmt.close();
            DBUtils.closeConnection(connection);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return bookBeans;
    }

    public boolean addbook(BookBean book)
    {
        int res;
        String sql="INSERT bm_book VALUES(null,?,?,?,?,?,?,?,?)";
        Connection connection = DBUtils.getConnection();
        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1,book.getBookName());
            pstmt.setInt(2,book.getTypeId());
            pstmt.setString(3,book.getAuthor());
            pstmt.setInt(4,book.getPublishID());
            pstmt.setFloat(5,book.getPrice());
            pstmt.setInt(6,book.getNumber());
            pstmt.setInt(7,book.getStatus());
            pstmt.setString(8,book.getRemark());
            res=pstmt.executeUpdate();
            pstmt.close();
            DBUtils.closeConnection(connection);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return res==1;
    }

    public BookBean getBookById(int id)
    {
       BookBean bookBean = null;
        String sql = "SELECT bm_book.*,bm_book_publish.`name` as publishName,bm_book_type.type_name as typeName \n" +
                "from bm_book,bm_book_type,bm_book_publish \n" +
                "WHERE bm_book.publish_id=bm_book_publish.id and bm_book.type_id=bm_book_type.id and bm_book.id=?";
        Connection connection = DBUtils.getConnection();
        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1,id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                String bookName = rs.getString("book_name");
                int typeId = rs.getInt("type_id");
                String author = rs.getString("author");
                int publishId = rs.getInt("publish_id");
                float price = rs.getFloat("price");
                int number = rs.getInt("number");
                int status = rs.getInt("status");
                String remark = rs.getString("remark");
                String publishName = rs.getString("publishName");
                String typeName = rs.getString("typeName");
                bookBean = new BookBean(id, bookName, typeId, typeName, author, publishId, publishName, price, number, status, remark);

            }
            pstmt.close();
            DBUtils.closeConnection(connection);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return bookBean;
    }

    public boolean updateBook(BookBean book)
    {
        int res;
        String sql="UPDATE bm_book set bm_book.author=? ,bm_book.book_name=? \n" +
                ",bm_book.number=?,bm_book.price=?,bm_book.publish_id=?,\n" +
                "bm_book.remark=? ,bm_book.`status`=?,bm_book.type_id=? where bm_book.id=?";
        Connection connection = DBUtils.getConnection();
        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1,book.getAuthor());
            pstmt.setString(2,book.getBookName());
            pstmt.setInt(3,book.getNumber());
            pstmt.setFloat(4,book.getPrice());
            pstmt.setInt(5,book.getPublishID());
            pstmt.setString(6,book.getRemark());
            pstmt.setInt(7,book.getStatus());
            pstmt.setInt(8,book.getTypeId());
            pstmt.setInt(9,book.getId());
             res = pstmt.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return res==1;
    }

}
